﻿
CREATE PROCEDURE [dbo].[fares_GenereazaJurnalVanzariTVALaIncasare] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@cumpTI bit
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
	CASE WHEN CuTVALaIncasare=1 THEN NrDataDocPlata ELSE '' END NrDataDocPlata,
	CASE WHEN CuTVALaIncasare=1 THEN Coalesce(ValPlatCuTVA,0) ELSE 0 END ValPlatCuTVA, 
	CASE WHEN CuTVALaIncasare=1 THEN Coalesce(BazaExigibila,0) ELSE 0 END BazaExigibila,
	CASE WHEN CuTVALaIncasare=1 THEN Coalesce(TVAExigibil,0) ELSE 0 END TVAExigibil,
	CASE WHEN CuTVALaIncasare=1 THEN doc.TotalValoare - Coalesce(BazaExigibila,0) ELSE 0 END BazaNeExigibila,
	CASE WHEN CuTVALaIncasare=1 THEN TotalValoareTVA  - Coalesce(TVAExigibil,0) ELSE 0 END TVANeExigibila,	
	docd.*
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
	(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument=11 THEN ValoareTotala ELSE ValoareTotala+ValoareTVA END ) ValoareTotalaSiTVA,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza24,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN ValoareTVA ELSE 0 END) TVA24,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza19,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN ValoareTVA ELSE 0 END) TVA19,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza9,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN ValoareTVA ELSE 0 END) TVA9,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) THEN ValoareTVA ELSE 0 END) TVA,	  
	  SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) InversBaza,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN ValoareTVA ELSE 0 END) InversTVA,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (61) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) OSRSTVA,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (62) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) PISCDD,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (63) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) PISFDD,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (64,14) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) LIBSCA143ad,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (65,15) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) LIBSCA143bc,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (66) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) EALBPSSDD,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (67) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) LPSFDD,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (68) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) ONRA,
	  CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END TI,
	  CASE WHEN dd.IDRegimFiscal IN (9,4) THEN 1 ELSE 0 END CuTVALaIncasare
	   FROM DocumenteDetaliu dd join Documente a on a.IDDocument=dd.IDDocument
	   GROUP BY dd.IDDocument,CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END,CASE WHEN dd.IDRegimFiscal IN (9,4) THEN 1 ELSE 0 END) docd ON doc.IDDocument = docd.IDDocument
	   LEFT OUTER JOIN
		(SELECT MAX(dp1.Numar +'/' +CONVERT(varchar(20),dp1.Data,104)) NrDataDocPlata,SUM(Valoare) ValPlatCuTVA,
		 SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		 FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
			WHERE dp1.Data>=@DeLaData AND dp1.Data<=@PanaLaData
			GROUP BY ddp1.IDDocument) selPlati ON selPlati.IDDocument = docd.IDDocument	
			
	   WHERE ((tipd.IDTipDocument in (1,11)) OR (@cumpTI=1 AND tipd.IDTipDocument in (0) AND TI=1))
		--AND LTRIM(RTRIM(part.AtributFiscal))<>''
		AND doc.CodUnitate=@CodUnitate 
		AND doc.DataDoc>=@DeLaData AND doc.DataDoc<=@PanaLaData
		
	UNION	---neexigibile mai mici de 90 de zile
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
	CASE WHEN CuTVALaIncasare=1 THEN selPlati.NrDataDocPlata ELSE '' END NrDataDocPlata,
	CASE WHEN CuTVALaIncasare=1 THEN Coalesce(selPlati.ValPlatCuTVA,0) ELSE 0 END ValPlatCuTVA, 
	CASE WHEN CuTVALaIncasare=1 THEN Coalesce(selPlati.BazaExigibila,0) ELSE 0 END BazaExigibila,
	CASE WHEN CuTVALaIncasare=1 THEN  Coalesce(selPlati.TVAExigibil,0)  ELSE 0 END TVAExigibil,
	CASE WHEN CuTVALaIncasare=1 THEN  doc.TotalValoare - COALESCE(selPlatiAnterior.BazaExigibila,0) -  Coalesce(selPlati.BazaExigibila,0) ELSE 0 END BazaNeExigibila,
	CASE WHEN CuTVALaIncasare=1 THEN  TotalValoareTVA  - COALESCE(selPlatiAnterior.TVAExigibil,0) - Coalesce(selPlati.TVAExigibil,0) ELSE 0 END TVANeExigibila,
	docd.*
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
	(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument=11 THEN ValoareTotala ELSE ValoareTotala+ValoareTVA END ) ValoareTotalaSiTVA,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza24,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN ValoareTVA ELSE 0 END) TVA24,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza19,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN ValoareTVA ELSE 0 END) TVA19,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza9,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN ValoareTVA ELSE 0 END) TVA9,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) THEN ValoareTVA ELSE 0 END) TVA,
	  
	  0 InversBaza,0 InversTVA,
	  0 OSRSTVA,
	  0 PISCDD,
	  0 PISFDD,
	  0 LIBSCA143ad,
	  0 LIBSCA143bc,
	  0 EALBPSSDD,
	  0 LPSFDD,
	  0 ONRA,
	  CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END TI,
	  CASE WHEN dd.IDRegimFiscal IN (9,4) THEN 1 ELSE 0 END CuTVALaIncasare
	   FROM DocumenteDetaliu dd join Documente a on a.IDDocument=dd.IDDocument
	   GROUP BY dd.IDDocument,CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END,CASE WHEN dd.IDRegimFiscal IN (9,4) THEN 1 ELSE 0 END) docd ON doc.IDDocument = docd.IDDocument
	   LEFT OUTER JOIN 
	(
		SELECT MAX(dp1.Numar +'/' +CONVERT(varchar(20),dp1.Data,104)) NrDataDocPlata,SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<=@PanaLaData
		GROUP BY ddp1.IDDocument
	) selPlati ON selPlati.IDDocument = docd.IDDocument LEFT OUTER JOIN 	
	(
		SELECT MAX(dp1.Numar +'/' +CONVERT(varchar(20),dp1.Data,104)) NrDataDocPlata,SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<@DeLaData 
		GROUP BY ddp1.IDDocument
	) selPlatiAnterior ON selPlatiAnterior.IDDocument = docd.IDDocument
			
	WHERE
	YEAR(doc.DataDoc) > 2012 AND                     -- dupa 2012  
	((tipd.IDTipDocument in (1)) OR (@cumpTI=1 AND tipd.IDTipDocument in (0) AND TI=1)) -- pentru facturile de vanzare fara bonuri fiscale
	AND doc.CodUnitate=@CodUnitate AND													-- pentru unitatea curenta
    --LTRIM(RTRIM(part.AtributFiscal))<>'' AND										    -- pentru partenerii care au atribut fiscal
    (((YEAR(DATEADD(day,90,doc.DataDoc))<=2013) AND (DATEADD(day,90,doc.DataDoc) > @PanaLaData)) OR (YEAR(DATEADD(day,90,doc.DataDoc))>=2014)) AND										-- dar nu mai vechi de 90 de zile
    doc.DataDoc<@DeLaData AND															-- pentru facturile de dinaintea intervalului selectat
	doc.IDRegimFiscal IN (4,9) AND
    --ValoareTotalaSiTVA > COALESCE(ValPlatCuTVA,0)
    EXISTS(SELECT * FROM DocumenteDetaliu dd WHERE dd.IDDocument = docd.IDDocument AND dd.TVAIncasare=1) AND 
																						-- pentru facturile care au o linie cu tVA la incasare
    ABS(doc.Total) >  ABS(COALESCE((SELECT SUM(dpd.Valoare) FROM DocumentePlataDetaliu dpd JOIN DocumentePlata dp ON dpd.IDDocumentPlata = dp.IDDocumentPlata
	WHERE dpd.IDDocument = docd.IDDocument AND dp.Data < @DeLaData),0))
																						-- care au ramas neachitate pana in perioada selectata 

    --CELE de 90 de zile devin exigibile in perioada curenta pentru facturile din 2012,2013
	UNION	
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
	CASE WHEN CuTVALaIncasare=1 THEN selPlati.NrDataDocPlata ELSE '' END NrDataDocPlata,
	CASE WHEN CuTVALaIncasare=1 THEN Coalesce(selPlati.ValPlatCuTVA,0) ELSE 0 END ValPlatCuTVA, 
	CASE WHEN CuTVALaIncasare=1 THEN doc.TotalValoare - COALESCE(selPlatiAnterior.BazaExigibila,0) ELSE 0 END BazaExigibila,
	CASE WHEN CuTVALaIncasare=1 THEN  TotalValoareTVA  - COALESCE(selPlatiAnterior.TVAExigibil,0)  ELSE 0 END TVAExigibil,
	CASE WHEN CuTVALaIncasare=1 THEN  0 ELSE 0 END BazaNeExigibila,
	CASE WHEN CuTVALaIncasare=1 THEN  0 ELSE 0 END TVANeExigibila,
	docd.*
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
	(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument=11 THEN ValoareTotala ELSE ValoareTotala+ValoareTVA END ) ValoareTotalaSiTVA,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza24,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN ValoareTVA ELSE 0 END) TVA24,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza19,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN ValoareTVA ELSE 0 END) TVA19,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza9,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN ValoareTVA ELSE 0 END) TVA9,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) THEN ValoareTVA ELSE 0 END) TVA,
	  
	  0 InversBaza,0 InversTVA,
	  0 OSRSTVA,
	  0 PISCDD,
	  0 PISFDD,
	  0 LIBSCA143ad,
	  0 LIBSCA143bc,
	  0 EALBPSSDD,
	  0 LPSFDD,
	  0 ONRA,
	  CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END TI,
	  CASE WHEN dd.IDRegimFiscal IN (9,4) THEN 1 ELSE 0 END CuTVALaIncasare
	   FROM DocumenteDetaliu dd join Documente a on a.IDDocument=dd.IDDocument
	   GROUP BY dd.IDDocument,CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END,CASE WHEN dd.IDRegimFiscal IN (9,4) THEN 1 ELSE 0 END) docd ON doc.IDDocument = docd.IDDocument
	   LEFT OUTER JOIN 
	(
		SELECT MAX(dp1.Numar +'/' +CONVERT(varchar(20),dp1.Data,104)) NrDataDocPlata,SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<=@PanaLaData
		GROUP BY ddp1.IDDocument
	) selPlati ON selPlati.IDDocument = docd.IDDocument LEFT OUTER JOIN 	
	(
		SELECT MAX(dp1.Numar +'/' +CONVERT(varchar(20),dp1.Data,104)) NrDataDocPlata,SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<@DeLaData 
		GROUP BY ddp1.IDDocument
	) selPlatiAnterior ON selPlatiAnterior.IDDocument = docd.IDDocument
			
	WHERE
	YEAR(doc.DataDoc) in (2012,2013) AND                     -- dupa 2012 si in 2013  
	((tipd.IDTipDocument in (1)) OR (@cumpTI=1 AND tipd.IDTipDocument in (0) AND TI=1)) -- pentru facturile de vanzare fara bonuri fiscale
	AND doc.CodUnitate=@CodUnitate AND													-- pentru unitatea curenta
    --LTRIM(RTRIM(part.AtributFiscal))<>'' AND										    -- pentru partenerii care au atribut fiscal
    DATEADD(day,90,doc.DataDoc) >= @DeLaData AND DATEADD(day,90,doc.DataDoc) <= @PanaLaData AND		-- dar nu mai vechi de 90 de zile
    YEAR(DATEADD(day,90,doc.DataDoc)) <=2013 AND										-- data dupa 90 de zile sa nu fie in 2014 
    doc.DataDoc<@DeLaData AND															-- pentru facturile de dinaintea intervalului selectat
	doc.IDRegimFiscal IN (4,9) AND
    --ValoareTotalaSiTVA > COALESCE(ValPlatCuTVA,0)
    EXISTS(SELECT * FROM DocumenteDetaliu dd WHERE dd.IDDocument = docd.IDDocument AND dd.TVAIncasare=1) AND 
																						-- pentru facturile care au o linie cu tVA la incasare
    ABS(doc.Total) >  ABS(COALESCE((SELECT SUM(dpd.Valoare) FROM DocumentePlataDetaliu dpd JOIN DocumentePlata dp ON dpd.IDDocumentPlata = dp.IDDocumentPlata
	WHERE dpd.IDDocument = docd.IDDocument AND dp.Data < @DeLaData),0))
																						-- care au ramas neachitate pana in perioada selectata 

	
	ORDER BY DataDoc
END